package at.spengergasse.dao.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import at.spengergasse.dao.HerstellerDao;
import at.spengergasse.dao.exception.DAOException;
import at.spengergasse.dao.exception.NotFoundException;
import at.spengergasse.dao.util.JDBCHelper;
import at.spengergasse.model.Hersteller;

public class HerstellerDaoDerbyImpl extends AbstractDAODerbyImpl implements
		HerstellerDao
{

	private static final String SQL_INSERT = "INSERT INTO Hersteller(h_mod_ts, h_name) "
			+ "VALUES (?, ?)";

	private static final String SQL_UPDATE = "UPDATE Hersteller "
			+ "SET h_mod_ts = ?, " + "h_name = ?" + "WHERE h_id = ? "
			+ "  AND h_mod_ts = ? ";

	private static final String SQL_DELETE = "DELETE FROM Hersteller "
			+ "WHERE h_id = ? " + "  AND h_mod_ts = ? ";

	private static final String SQL_SELECT_BY_ID = "SELECT h_id, h_mod_ts, h_name"
			+ " FROM Hersteller " + "WHERE h_id = ?";

	private static final String SQL_SELECT_ALL = "SELECT h_id, h_mod_ts, h_name "
			+ "from Hersteller";

	private PreparedStatement insertStmt = null;
	private PreparedStatement updateStmt = null;
	private PreparedStatement deleteStmt = null;
	private PreparedStatement selectByIdStmt = null;
	private PreparedStatement selectAllStmt = null;

	@Override
	public int delete(Connection connection, Hersteller hersteller)
	{
		validateConnection(connection);
		validateHersteller(hersteller);

		int recordsAffected = 0;

		try
		{
			if (deleteStmt == null)
			{
				deleteStmt = connection.prepareStatement(SQL_DELETE);
			}

			deleteStmt.setLong(1, hersteller.getId());
			deleteStmt.setTimestamp(2, JDBCHelper.toSqlTimestamp(hersteller
					.getLastModifiedTS()));

			recordsAffected = deleteStmt.executeUpdate();
		} catch (SQLException e)
		{
			throw new DAOException("Hersteller could not be deleted!", e);
		}
		return recordsAffected;
	}

	@Override
	public List<Hersteller> findAll(Connection connection)
	{
		validateConnection(connection);

		List<Hersteller> foundHersteller = new ArrayList<Hersteller>();
		Hersteller foundHerstell = null;
		ResultSet rs = null;

		try
		{
			if (selectAllStmt == null)
			{
				selectAllStmt = connection.prepareStatement(SQL_SELECT_ALL);
			}
			rs = selectAllStmt.executeQuery();

			while (rs.next())
			{
				foundHerstell = new Hersteller(rs.getLong(1), rs.getString(3));
				foundHerstell.setLastModifiedTS(JDBCHelper.toDate(rs
						.getTimestamp(2)));
				foundHersteller.add(foundHerstell);
			}
		} catch (SQLException e)
		{
			throw new NotFoundException("Hersteller could not be read!", e);
		} finally
		{
			closeResultSet(rs);
		}

		return foundHersteller;
	}

	@Override
	public Hersteller findById(Connection connection, Long id)
	{
		validateConnection(connection);
		if (id == null)
			throw new IllegalArgumentException("Id darf nicht null sein!");

		Hersteller foundHersteller = null;
		ResultSet rs = null;
		try
		{
			if (selectByIdStmt == null)
			{
				selectByIdStmt = connection.prepareStatement(SQL_SELECT_BY_ID);
			}

			selectByIdStmt.setLong(1, id);
			rs = selectByIdStmt.executeQuery();

			while (rs.next())
			{
				foundHersteller = new Hersteller(id, rs.getString(3));
				foundHersteller.setLastModifiedTS(JDBCHelper.toDate(rs
						.getTimestamp(2)));
			}
		} catch (SQLException e)
		{
			throw new NotFoundException("Hersteller could not be found!", e);
		} finally
		{
			closeResultSet(rs);
		}

		return foundHersteller;
	}

	@Override
	public int save(Connection connection, Hersteller hersteller)
	{
		validateConnection(connection);
		validateHersteller(hersteller);
		return (hersteller.isNew()) ? insert(connection, hersteller) : update(
				connection, hersteller);
	}

	private int update(Connection connection, Hersteller hersteller)
	{
		validateConnection(connection);
		validateHersteller(hersteller);

		int recordsAffected = 0;
		Date now = new Date();

		try
		{
			if (updateStmt == null)
			{
				updateStmt = connection.prepareStatement(SQL_UPDATE);
			}

			updateStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
			updateStmt.setString(2, hersteller.getHersteller());
			updateStmt.setLong(3, hersteller.getId());
			updateStmt.setTimestamp(4, JDBCHelper.toSqlTimestamp(hersteller
					.getLastModifiedTS()));

			recordsAffected = updateStmt.executeUpdate();

			hersteller.setLastModifiedTS(now);
		} catch (SQLException e)
		{
			throw new DAOException("Hersteller could not be updated!", e);
		}
		return recordsAffected;
	}

	private int insert(Connection connection, Hersteller hersteller)
	{
		validateConnection(connection);
		validateHersteller(hersteller);

		int recordsAffected = 0;
		Date now = new Date();
		ResultSet generatedKeys = null;

		try
		{
			if (insertStmt == null)
			{
				insertStmt = connection.prepareStatement(SQL_INSERT,
						PreparedStatement.RETURN_GENERATED_KEYS);
			}

			insertStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
			insertStmt.setString(2, hersteller.getHersteller());

			recordsAffected = insertStmt.executeUpdate();
			generatedKeys = insertStmt.getGeneratedKeys();

			if (generatedKeys.next())
			{
				long currentId = generatedKeys.getLong(1);
				hersteller.setId(currentId);
				hersteller.setLastModifiedTS(now);
			}
		} catch (SQLException e)
		{
			throw new DAOException("Hersteller could not be inserted!", e);
		} finally
		{
			closeResultSet(generatedKeys);
		}
		return recordsAffected;
	}

	private void validateHersteller(Hersteller hersteller)
	{
		if (hersteller == null)
			throw new IllegalArgumentException(
					"Zu speichernde Hersteller darf nicht null sein");

	}

}
